package de.herzog.mtv.bb;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelFile {

	private String fileName;
	private HSSFWorkbook workbook;
	private Map<Integer, String> headers;
	private Map<String, Integer> indexes;
	private List<ExcelRow> rows;

	public ExcelFile(String fileName) throws FileNotFoundException, IOException {
		this.fileName = fileName;
	}

	public ExcelFile load() throws IOException, FileNotFoundException {
		workbook = new HSSFWorkbook(new FileInputStream(fileName));
		headers = new HashMap<Integer, String>();
		indexes = new HashMap<String, Integer>();
		HSSFRow headerRow = workbook.getSheetAt(0).getRow(0);
		for (int cellNum = headerRow.getFirstCellNum(); cellNum < headerRow.getLastCellNum(); cellNum++) {
			String cellValue = headerRow.getCell(cellNum).getStringCellValue();
			headers.put(cellNum, cellValue);
			indexes.put(cellValue, cellNum);
		}
		rows = new ArrayList<ExcelRow>();
		HSSFSheet sheet = workbook.getSheetAt(0);
		for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
			HSSFRow row = sheet.getRow(rowIndex);
			ExcelRow excelRow = new ExcelRow(row, headers, rowIndex);
			rows.add(excelRow);
		}
		return this;
	}

	public List<ExcelRow> getRows() throws FileNotFoundException, IOException {
		return rows;
	}

	public ExcelRow getRow(String columnName1, String columnValue1, String columnName2, String columnValue2)
			throws FileNotFoundException, IOException {
		for (ExcelRow excelRow : getRows()) {
			if (excelRow.hasColumnValue(columnName1, columnValue1)
					&& excelRow.hasColumnValue(columnName2, columnValue2)) {
				return excelRow;
			}
		}
		return null;
	}

	public void updateRow(ExcelRow excelRow, String columnName, Object columnValue) {
		excelRow.setColumn(columnName, columnValue);
		HSSFRow row = workbook.getSheetAt(0).getRow(excelRow.getRowIndex());
		int columnIndex = indexes.get(columnName);
		HSSFCell cell = row.getCell(columnIndex);
		if (cell == null) {
			cell = row.createCell(columnIndex);
		}
		if (columnValue instanceof String) {
			cell.setCellValue((String) columnValue);
		} else if (columnValue instanceof Date) {
			cell.setCellValue((Date) columnValue);
		} else if (columnValue instanceof Boolean) {
			cell.setCellValue((Boolean) columnValue);
		} else if (columnValue instanceof Number) {
			cell.setCellValue(((Number) columnValue).doubleValue());
		}
	}

	public void addRow(Map<String, Object> values) {
		HSSFSheet sheet = workbook.getSheetAt(0);
		int rowIndex = sheet.getLastRowNum() + 1;
		sheet.createRow(rowIndex);
		ExcelRow excelRow = new ExcelRow(rowIndex);
		rows.add(excelRow);
		for (Map.Entry<String, Object> entry : values.entrySet()) {
			updateRow(excelRow, entry.getKey(), entry.getValue());
		}
	}

	public void save() throws IOException {
		FileOutputStream out = new FileOutputStream(fileName);
		workbook.write(out);
		out.close();
	}
}
